package cn.ydxiaoshuai.util;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.record.crypto.Biff8EncryptionKey;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.crypt.Decryptor;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

/**
 * Description Excel带密码文件读取工具类 没有一一处理。主要在读取带密码的excel文件
 * ProjectName worktools
 * Created by 小帅丶 on 2022-04-18
 * Version 1.0
 */

public class ExcelDealUtil {
    /**
     * @Description 读取Excel内容
     * @param excelPath - excel文件
     * @param password - 文件密码
     * @param startRow - 开始读取数据的行
     * @Author 小帅丶
     * @Date  2022/4/18 15:31
     **/
    public static void getExcelData(String excelPath,String password,Integer startRow)throws Exception{
        InputStream inputStream = new FileInputStream(new File(excelPath));
        boolean isExcel2003 = true;
        if (excelPath.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        getExcelData(isExcel2003,inputStream,password,startRow);
    }
    /**
     * @Description 读取Excel内容
     * @param file     - 文件对象
     * @param password - 文件密码
     * @param startRow - 开始读取数据的行
     * @Author 小帅丶
     * @Date 2022/4/18 15:31
     **/
    public static void getExcelData(MultipartFile file, String password,
                                       Integer startRow) throws Exception {
        InputStream inputStream = file.getInputStream();
        boolean isExcel2003 = true;
        String fileName = file.getOriginalFilename();
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        getExcelData(isExcel2003, inputStream, password, startRow);
    }

    /**
     * @Description 读取Excel文件内容
     * @param isExcel2003 - 是否为xls
     * @param inputStream - 文件流
     * @param password    - 文件密码
     * @param startRow    - 开始读取数据行索引
     * @Author 小帅丶
     * @Date 2022/4/18 19:01
     **/
    private static void getExcelData(boolean isExcel2003, InputStream inputStream,
                                     String password, Integer startRow) throws Exception {
        if (isExcel2003) {
            readXLSFileData(inputStream, password, startRow);
        } else {
            readXLSXFileData(inputStream, password, startRow);
        }
        inputStream.close();
    }

    /**
     * @Description 读取excel文件内容
     * @param inputStream - 文件流
     * @param password    - 密码
     * @param startRow    - 开始读取数据的行索引
     * @Author 小帅丶
     * @Date 2022/4/18 18:57
     **/
    private static void readXLSXFileData(InputStream inputStream, String password,
                                         Integer startRow) throws Exception {
        XSSFWorkbook workbook = null;
        POIFSFileSystem pfs = new POIFSFileSystem(inputStream);
        EncryptionInfo encInfo = new EncryptionInfo(pfs);
        Decryptor decryptor = Decryptor.getInstance(encInfo);
        boolean verifyPassword = decryptor.verifyPassword(password);
        if (verifyPassword) {
            workbook = new XSSFWorkbook(decryptor.getDataStream(pfs));
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                XSSFSheet sheetAt = workbook.getSheetAt(i);
                for (int j = 0; j < sheetAt.getPhysicalNumberOfRows() - startRow; j++) {
                    XSSFRow row = sheetAt.getRow(j + startRow);
                    dealData(row);
                }
            }
            pfs.close();
        } else {
            System.out.println("密码错误");
        }
    }

    /**
     * @Description 读取excel文件内容
     * @param inputStream - 文件流
     * @param password    - 密码
     * @param startRow    - 开始读取数据的行索引
     * @Author 小帅丶
     * @Date 2022/4/18 18:57
     **/
    private static void readXLSFileData(InputStream inputStream, String password,
                                        Integer startRow) throws Exception {
        HSSFWorkbook workbook = null;
        Biff8EncryptionKey.setCurrentUserPassword(password);
        try {
            workbook = new HSSFWorkbook(inputStream);
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                HSSFSheet sheetAt = workbook.getSheetAt(i);
                for (int j = 0; j < sheetAt.getPhysicalNumberOfRows() - startRow; j++) {
                    HSSFRow row = sheetAt.getRow(j + startRow);
                    dealData(row);
                }
            }
        } catch (EncryptedDocumentException e) {
            System.out.println("密码错误");
        }
    }

    /**
     * @Description 处理Excel读取到的内容
     * @param row - 行对象
     * @Author 小帅丶
     * @Date 2022/4/18 18:57
     **/
    private static void dealData(Row row) throws Exception {
        if (null != row) {
            for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {
                getExcelValue(row.getCell(k));
            }
        }
    }

    /**
     * @Description 获取指定行列的数据内容 没有一一处理。主要在读取带密码的excel文件
     * @param cell - 列对象
     * @Author 小帅丶
     * @Date 2022/4/18 18:57
     **/
    private static void getExcelValue(Cell cell) throws Exception {
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
        switch (cell.getCellType()) {
            case STRING:
                cell.getStringCellValue();
                break;
            case NUMERIC:
                cell.getNumericCellValue();
                if (DateUtil.isCellDateFormatted(cell)) {
                    dateFormat.format(cell.getDateCellValue());
                }
                break;
            default:
                break;
        }
    }
}
